const pool = require("../db/conn");
const countControllers = require("../controllers/countControllers");

exports.selectList = async function (pageno,pagesize,courseName,major,className) {    
    let sql =   " SELECT "+
                " 	rc_course.course_name,  "+
                " 	rc_course.course_id,  "+
                " 	rc_student.student_id,  "+
                " 	rc_student.student_name,  "+
                " 	rc_class.class_id,  "+
                " 	rc_class.class_name,  "+
                " 	rc_student_course.sc_daily_score,  "+
                " 	rc_student_course.sc_exam_score,  "+
                " 	rc_student_course.sc_score,  "+
                " 	rc_major.major_id,  "+
                " 	rc_major.major_name "+
                " FROM "+
                " 	rc_student_course "+
                " 	LEFT JOIN "+
                " 	rc_student "+
                " 	ON  "+
                " 		rc_student_course.sc_student_id = rc_student.student_id "+
                " 	LEFT JOIN "+
                " 	rc_class "+
                " 	ON  "+
                " 		rc_student.student_class_id = rc_class.class_id "+
                " 	LEFT JOIN "+
                " 	rc_course "+
                " 	ON  "+
                " 		rc_student_course.sc_course_id = rc_course.course_id "+
                " 	LEFT JOIN "+
                " 	rc_major "+
                " 	ON  "+
                " 		rc_class.class_major_id = rc_major.major_id "+
                " WHERE 1=1 ";
    let params = [];
    if(courseName!=null&&courseName!=""){
        sql += "AND rc_course.course_name like ? "
        params.push( `%${courseName}%` );
    }
    if(major!=null&&major!=""){
        sql += "AND rc_major.major_name like ? "
        params.push( `%${major}%` );
    }
    if(className!=null&&className!=""){
        sql += "AND rc_class.class_name like ? "
        params.push( `%${className}%` );
    }

    let data = await countControllers.selectCount( sql,params );
    
    sql += "LIMIT ?, ?";
    let start = ((pageno-1)*pagesize)+"";
    let size = pagesize;
    
    params.push(start,size);

    let conn = await pool.getConnection();
    let [result] = await conn.execute(sql,params);
    conn.release();

    data.pageNo = pageno*1
    data.pageSize = pagesize*1;
    data.list = result;
    return data;
}

exports.updateCourseSource = async function(daily_score,exam_score,score,id){
    let sql = "UPDATE rc_student_course SET sc_daily_score = ?, sc_exam_score = ?, sc_score = ? WHERE sc_id = ?";

    let conn = await pool.getConnection();
    let [{changedRows}] = await conn.execute(sql,[daily_score,exam_score,score,id]);
    conn.release();
    
    return changedRows!=0;
}